package o2o.task.order;


import com.jinyou.utils.common.DateUtils;
import com.jinyou.utils.common.ValidateUtil;
import o2o.config.dao.DataDaoSql;
import o2o.config.dao.UserDaoSql;
import o2o.dao.PO.PojoTables;
import o2o.system.data.SYS_SETTINGS;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.HashSet;

// 订单备份处理
public class OrderBakTaskUtils {

    // data库名称
    private static String dataDbName = null;
    // 待备份订单列表
    public static HashSet<String> orderSet = new HashSet<>();

    public static int autoBackDays = 93;

    // 备份订单（主要用于财务概览等数据的查询能够实时的查询到）
    public static void backOrder() {
        if (null == orderSet || orderSet.size() < 1) {
            // 没有需要备份的订单
            return;
        }
        HashSet<String> hashSet = new HashSet<>(orderSet);
        if (null == hashSet || hashSet.size() < 1) {
            // 没有需要备份的订单
            return;
        }

        StringBuffer sb = new StringBuffer();
        for (String orderNo : hashSet) {
            if (ValidateUtil.isNull(orderNo)) {
                continue;
            }
            sb.append("'").append(orderNo).append("',");

            delOrder(orderNo);
        }

        String orderStr = null;
        if (sb.length() > 0) {
            orderStr = sb.substring(0, sb.length() - 1);
        }
        if (ValidateUtil.isNull(orderStr)) {
            return;
        }

        if (ValidateUtil.isNull(dataDbName)) {
            HashMap<String, Object> map = null;
            try {
                map = DataDaoSql.getInstance().queryMapBySql("SELECT DATABASE() db;");
            } catch (SQLException e) {
                e.printStackTrace();
                return;
            }
            if (null == map) {
                return;
            }
            dataDbName = map.get("db").toString();
            if (ValidateUtil.isNull(dataDbName)) {
                return;
            }
        }

//        StringBuffer sbSql = new StringBuffer();
//        sbSql.append("INSERT INTO  ").append(dataDbName).append(".").append(PojoTables.D_ORDER_INFO).append("  SELECT * FROM ").append(PojoTables.OrderInfo).append(" WHERE orderNo IN (").append(orderStr).append(");");
//        sbSql.append("INSERT INTO  ").append(dataDbName).append(".").append(PojoTables.D_ORDER_GOODS).append("  SELECT * FROM ").append(PojoTables.OrderGoods).append(" WHERE orderNo IN (").append(orderStr).append(");");
//        sbSql.append("INSERT INTO  ").append(dataDbName).append(".").append(PojoTables.D_ORDER_STATUS).append("  SELECT * FROM ").append(PojoTables.OrderStatus).append(" WHERE orderNo IN (").append(orderStr).append(");");
//        sbSql.append("INSERT INTO  ").append(dataDbName).append(".").append(PojoTables.D_ORDER_PAY).append("  SELECT * FROM ").append(PojoTables.OrderPay).append(" WHERE orderNo IN (").append(orderStr).append(");");
//        sbSql.append("INSERT INTO  ").append(dataDbName).append(".").append(PojoTables.D_ORDER_INFO_EXTRA).append("  SELECT * FROM ").append(PojoTables.ORDER_INFO_EXTRA).append(" WHERE orderNo IN (").append(orderStr).append(");");
//        sbSql.append("INSERT INTO  ").append(dataDbName).append(".").append(PojoTables.D_USER_WALLET_FLOW).append("  SELECT * FROM ").append(PojoTables.USER_WALLET_FLOW).append(" WHERE orderNo IN (").append(orderStr).append(");");
//        try {
//            UserDaoSql.getInstance().executeBySql(sbSql.toString());
//        } catch (SQLException e) {
//            e.printStackTrace();
//            return;
//        }

        try {
            StringBuffer sbSql = new StringBuffer();
            sbSql.append("INSERT INTO  ").append(dataDbName).append(".").append(PojoTables.D_ORDER_INFO).append("  SELECT * FROM ").append(PojoTables.OrderInfo).append(" WHERE orderNo IN (").append(orderStr).append(");");
            UserDaoSql.getInstance().executeBySql(sbSql.toString());

        } catch (SQLException e) {
            e.printStackTrace();
            return;
        }
        try {
            StringBuffer sbSql = new StringBuffer();
            sbSql.append("INSERT INTO  ").append(dataDbName).append(".").append(PojoTables.D_ORDER_GOODS).append("  SELECT * FROM ").append(PojoTables.OrderGoods).append(" WHERE orderNo IN (").append(orderStr).append(");");
            UserDaoSql.getInstance().executeBySql(sbSql.toString());

        } catch (SQLException e) {
            e.printStackTrace();
            return;
        }
        try {
            StringBuffer sbSql = new StringBuffer();
            sbSql.append("INSERT INTO  ").append(dataDbName).append(".").append(PojoTables.D_ORDER_STATUS).append("  SELECT * FROM ").append(PojoTables.OrderStatus).append(" WHERE orderNo IN (").append(orderStr).append(");");
            UserDaoSql.getInstance().executeBySql(sbSql.toString());

        } catch (SQLException e) {
            e.printStackTrace();
            return;
        }
        try {
            StringBuffer sbSql = new StringBuffer();
            sbSql.append("INSERT INTO  ").append(dataDbName).append(".").append(PojoTables.D_ORDER_PAY).append("  SELECT * FROM ").append(PojoTables.OrderPay).append(" WHERE orderNo IN (").append(orderStr).append(");");
            UserDaoSql.getInstance().executeBySql(sbSql.toString());

        } catch (SQLException e) {
            e.printStackTrace();
            return;
        }
        try {
            StringBuffer sbSql = new StringBuffer();
            sbSql.append("INSERT INTO  ").append(dataDbName).append(".").append(PojoTables.D_ORDER_INFO_EXTRA).append("  SELECT * FROM ").append(PojoTables.ORDER_INFO_EXTRA).append(" WHERE orderNo IN (").append(orderStr).append(");");
            UserDaoSql.getInstance().executeBySql(sbSql.toString());

        } catch (SQLException e) {
            e.printStackTrace();
            return;
        }
        try {
            StringBuffer sbSql = new StringBuffer();
            sbSql.append("INSERT INTO  ").append(dataDbName).append(".").append(PojoTables.D_USER_WALLET_FLOW).append("  SELECT * FROM ").append(PojoTables.USER_WALLET_FLOW).append(" WHERE orderNo IN (").append(orderStr).append(");");
            UserDaoSql.getInstance().executeBySql(sbSql.toString());

        } catch (SQLException e) {
            e.printStackTrace();
            return;
        }
    }


    // 清除已备份订单
    public static void delOldOrder(Integer days) {
        if (ValidateUtil.isNotAbsInteger(days)) {
            return;
        }
        if (days < 30) {
            return;
        }
        Long sec = DateUtils.getPreDaysOfTimestamp(Long.parseLong(days + ""));
        if (ValidateUtil.isNull(dataDbName)) {
            HashMap<String, Object> map = null;
            try {
                map = DataDaoSql.getInstance().queryMapBySql("SELECT DATABASE() db;");
            } catch (SQLException e) {
                e.printStackTrace();
                return;
            }
            if (null == map) {
                return;
            }
            dataDbName = map.get("db").toString();
            if (ValidateUtil.isNull(dataDbName)) {
                return;
            }
        }

//        StringBuffer sbSql = new StringBuffer();
//        sbSql.append("DELETE a FROM ").append(PojoTables.OrderInfo).append(" a, ").append(dataDbName).append(".").append(PojoTables.D_ORDER_INFO).append(" b WHERE a.id = b.id AND a.orderNo = b.orderNo  and a.createTime< ").append(sec).append("; ");
//        sbSql.append("DELETE a FROM ").append(PojoTables.OrderGoods).append(" a, ").append(dataDbName).append(".").append(PojoTables.D_ORDER_GOODS).append(" b WHERE a.id = b.id AND a.orderNo = b.orderNo  and a.createTime< ").append(sec).append("; ");
//        sbSql.append("DELETE a FROM ").append(PojoTables.OrderStatus).append(" a, ").append(dataDbName).append(".").append(PojoTables.D_ORDER_STATUS).append(" b WHERE a.id = b.id AND a.orderNo = b.orderNo  and a.createTime< ").append(sec).append("; ");
//        sbSql.append("DELETE a FROM ").append(PojoTables.OrderPay).append(" a, ").append(dataDbName).append(".").append(PojoTables.D_ORDER_PAY).append(" b WHERE a.id = b.id AND a.orderNo = b.orderNo  and a.createTime< ").append(sec).append("; ");
//        sbSql.append("DELETE a FROM ").append(PojoTables.ORDER_INFO_EXTRA).append(" a, ").append(dataDbName).append(".").append(PojoTables.D_ORDER_INFO_EXTRA).append(" b WHERE a.id = b.id AND a.orderNo = b.orderNo  and a.createTime< ").append(sec).append("; ");
//        sbSql.append("DELETE a FROM ").append(PojoTables.USER_WALLET_FLOW).append(" a, ").append(dataDbName).append(".").append(PojoTables.D_USER_WALLET_FLOW).append(" b WHERE a.id = b.id AND a.orderNo = b.orderNo  and a.createTime< ").append(sec).append("; ");
//        try {
//            UserDaoSql.getInstance().executeBySql(sbSql.toString());
//        } catch (SQLException e) {
//            e.printStackTrace();
//            return;
//        }


        try {
            StringBuffer sbSql = new StringBuffer();
            sbSql.append("DELETE a FROM ").append(PojoTables.OrderInfo).append(" a, ").append(dataDbName).append(".").append(PojoTables.D_ORDER_INFO).append(" b WHERE a.id = b.id AND a.orderNo = b.orderNo  and a.createTime< ").append(sec).append("; ");
            UserDaoSql.getInstance().executeBySql(sbSql.toString());

        } catch (SQLException e) {
            e.printStackTrace();
            return;
        }
        try {
            StringBuffer sbSql = new StringBuffer();
            sbSql.append("DELETE a FROM ").append(PojoTables.OrderGoods).append(" a, ").append(dataDbName).append(".").append(PojoTables.D_ORDER_GOODS).append(" b WHERE a.id = b.id AND a.orderNo = b.orderNo  and a.createTime< ").append(sec).append("; ");
            UserDaoSql.getInstance().executeBySql(sbSql.toString());

        } catch (SQLException e) {
            e.printStackTrace();
            return;
        }
        try {
            StringBuffer sbSql = new StringBuffer();
            sbSql.append("DELETE a FROM ").append(PojoTables.OrderStatus).append(" a, ").append(dataDbName).append(".").append(PojoTables.D_ORDER_STATUS).append(" b WHERE a.id = b.id AND a.orderNo = b.orderNo  and a.createTime< ").append(sec).append("; ");
            UserDaoSql.getInstance().executeBySql(sbSql.toString());

        } catch (SQLException e) {
            e.printStackTrace();
            return;
        }
        try {
            StringBuffer sbSql = new StringBuffer();
            sbSql.append("DELETE a FROM ").append(PojoTables.OrderPay).append(" a, ").append(dataDbName).append(".").append(PojoTables.D_ORDER_PAY).append(" b WHERE a.id = b.id AND a.orderNo = b.orderNo  and a.createTime< ").append(sec).append("; ");
            UserDaoSql.getInstance().executeBySql(sbSql.toString());

        } catch (SQLException e) {
            e.printStackTrace();
            return;
        }
        try {
            StringBuffer sbSql = new StringBuffer();
            sbSql.append("DELETE a FROM ").append(PojoTables.ORDER_INFO_EXTRA).append(" a, ").append(dataDbName).append(".").append(PojoTables.D_ORDER_INFO_EXTRA).append(" b WHERE a.id = b.id AND a.orderNo = b.orderNo  and a.createTime< ").append(sec).append("; ");
            UserDaoSql.getInstance().executeBySql(sbSql.toString());

        } catch (SQLException e) {
            e.printStackTrace();
            return;
        }
        try {
            StringBuffer sbSql = new StringBuffer();
            sbSql.append("DELETE a FROM ").append(PojoTables.USER_WALLET_FLOW).append(" a, ").append(dataDbName).append(".").append(PojoTables.D_USER_WALLET_FLOW).append(" b WHERE a.id = b.id AND a.orderNo = b.orderNo  and a.createTime< ").append(sec).append("; ");
            UserDaoSql.getInstance().executeBySql(sbSql.toString());

        } catch (SQLException e) {
            e.printStackTrace();
            return;
        }
    }

    // 备份订单（主要用于财务概览等数据的查询能够实时的查询到）
    public static void autoBackOrder(Integer days) {
        if (ValidateUtil.isNull(dataDbName)) {
            dataDbName = getDataDbName();
            if (ValidateUtil.isNull(dataDbName)) {
                return;
            }
        }
        if (null == days) {
            days = autoBackDays;
        }
        Long sec = DateUtils.getPreDaysOfTimestamp(Long.parseLong(days + ""));

        try {
            StringBuffer sbSql = new StringBuffer();
            sbSql.append(" INSERT INTO  ").append(dataDbName).append(".").append(PojoTables.D_ORDER_INFO)
                    .append("  SELECT * FROM ").append(PojoTables.OrderInfo).append(" a WHERE NOT EXISTS(SELECT * FROM ").append(dataDbName).append(".").append(PojoTables.D_ORDER_INFO).append(" b WHERE a.id=b.id) and a.createTime < ").append(sec).append(" ; ");
            UserDaoSql.getInstance().executeBySql(sbSql.toString());

        } catch (SQLException e) {
            e.printStackTrace();
            return;
        }
        try {
            StringBuffer sbSql = new StringBuffer();
            sbSql.append(" INSERT INTO  ").append(dataDbName).append(".").append(PojoTables.D_ORDER_GOODS)
                    .append("  SELECT * FROM ").append(PojoTables.OrderGoods).append(" a WHERE NOT EXISTS(SELECT * FROM ").append(dataDbName).append(".").append(PojoTables.D_ORDER_GOODS).append(" b WHERE a.id=b.id) and a.createTime < ").append(sec).append("; ");
            UserDaoSql.getInstance().executeBySql(sbSql.toString());

        } catch (SQLException e) {
            e.printStackTrace();
            return;
        }
        try {
            StringBuffer sbSql = new StringBuffer();
            sbSql.append(" INSERT INTO  ").append(dataDbName).append(".").append(PojoTables.D_ORDER_STATUS)
                    .append("  SELECT * FROM ").append(PojoTables.OrderStatus).append("  a WHERE NOT EXISTS(SELECT * FROM ").append(dataDbName).append(".").append(PojoTables.D_ORDER_STATUS).append(" b WHERE a.id=b.id) and a.createTime < ").append(sec).append(" ; ");
            UserDaoSql.getInstance().executeBySql(sbSql.toString());

        } catch (SQLException e) {
            e.printStackTrace();
            return;
        }
        try {
            StringBuffer sbSql = new StringBuffer();
            sbSql.append(" INSERT INTO  ").append(dataDbName).append(".").append(PojoTables.D_ORDER_PAY)
                    .append("  SELECT * FROM ").append(PojoTables.OrderPay).append("  a WHERE NOT EXISTS(SELECT * FROM ").append(dataDbName).append(".").append(PojoTables.D_ORDER_PAY).append(" b WHERE a.id=b.id) and a.createTime < ").append(sec).append(" ; ");
            UserDaoSql.getInstance().executeBySql(sbSql.toString());

        } catch (SQLException e) {
            e.printStackTrace();
            return;
        }
        try {
            StringBuffer sbSql = new StringBuffer();
            sbSql.append(" INSERT INTO  ").append(dataDbName).append(".").append(PojoTables.D_ORDER_INFO_EXTRA)
                    .append("  SELECT * FROM ").append(PojoTables.ORDER_INFO_EXTRA).append(" a WHERE NOT EXISTS(SELECT * FROM ").append(dataDbName).append(".").append(PojoTables.D_ORDER_INFO_EXTRA).append(" b WHERE a.id=b.id) and a.createTime < ").append(sec).append(" ; ");
            UserDaoSql.getInstance().executeBySql(sbSql.toString());

        } catch (SQLException e) {
            e.printStackTrace();
            return;
        }
        try {
            StringBuffer sbSql = new StringBuffer();
            sbSql.append(" INSERT INTO  ").append(dataDbName).append(".").append(PojoTables.D_USER_WALLET_FLOW)
                    .append("  SELECT * FROM ").append(PojoTables.USER_WALLET_FLOW).append(" a WHERE NOT EXISTS(SELECT * FROM ").append(dataDbName).append(".").append(PojoTables.D_USER_WALLET_FLOW).append(" b WHERE a.id=b.id) and a.createTime < ").append(sec).append(" ; ");
            UserDaoSql.getInstance().executeBySql(sbSql.toString());

        } catch (SQLException e) {
            e.printStackTrace();
            return;
        }


    }

    // 增加需要备份的订单
    public static void addOrder(String orderNo) {
        if (null != SYS_SETTINGS.USE_BAK_ORDER && 0 == 1 - SYS_SETTINGS.USE_BAK_ORDER) {

        } else {
            return;
        }
        if (ValidateUtil.isNull(orderNo)) {
            return;
        }
        if (null == orderSet) {
            orderSet = new HashSet<>();
        }
        orderSet.add(orderNo);
    }

    // 删除一备份订单
    private static void delOrder(String orderNo) {
        if (ValidateUtil.isNull(orderNo)) {
            return;
        }
        if (null == orderSet) {
            return;
        }
        orderSet.remove(orderNo);
    }

    public static String getDataDbName() {
        if (ValidateUtil.isNotNull(dataDbName)) {
            return dataDbName;
        }
        HashMap<String, Object> map = null;
        try {
            map = DataDaoSql.getInstance().queryMapBySql("SELECT DATABASE() db;");
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
        if (null == map) {
            return null;
        }
        dataDbName = map.get("db").toString();
        if (ValidateUtil.isNull(dataDbName)) {
            return null;
        }

        return dataDbName;
    }
}
